#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
清理无效记录
执行此脚本将删除期号与日期不匹配的记录
⚠️ 警告：此操作不可逆，请确保已执行 02_backup_database.py 进行备份！
"""

import pymysql
import sys
import os

# 添加父目录到路径
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from crawler.config.settings import DATABASE_CONFIG

def clean_invalid_records(dry_run=True):
    conn = pymysql.connect(**DATABASE_CONFIG)
    cursor = conn.cursor()
    
    lottery_types = {
        'ssq': '双色球',
        'fc3d': '福彩3D',
        'qlc': '七乐彩',
        'kl8': '快乐8'
    }
    
    print("=" * 100)
    if dry_run:
        print("【模拟运行模式】清理无效记录（不会真正删除数据）")
    else:
        print("【正式运行模式】清理无效记录")
    print("=" * 100)
    
    total_deleted = 0
    total_found = 0
    
    for lottery_type, name in lottery_types.items():
        print(f"\n处理 {name} ({lottery_type})...")
        
        # 查询需要删除的记录
        cursor.execute("""
            SELECT id, period, draw_date
            FROM lottery_results
            WHERE lottery_type = %s
            AND (
                (period >= 2000000 AND CAST(SUBSTRING(CAST(period AS CHAR), 1, 4) AS UNSIGNED) != YEAR(draw_date))
                OR (period >= 100000 AND period < 2000000 AND CAST(CONCAT('20', SUBSTRING(CAST(period AS CHAR), 1, 2)) AS UNSIGNED) != YEAR(draw_date))
            )
        """, (lottery_type,))
        
        invalid_records = cursor.fetchall()
        
        if invalid_records:
            print(f"  发现 {len(invalid_records)} 条无效记录")
            total_found += len(invalid_records)
            
            if not dry_run:
                # 先删除遗漏数据
                invalid_periods = [record[1] for record in invalid_records]
                placeholders = ','.join(['%s'] * len(invalid_periods))
                
                cursor.execute(f"""
                    DELETE FROM lottery_omission
                    WHERE lottery_type = %s AND period IN ({placeholders})
                """, [lottery_type] + invalid_periods)
                
                deleted_omission = cursor.rowcount
                print(f"    - 已删除 {deleted_omission} 条相关遗漏记录")
                
                # 删除开奖结果
                cursor.execute(f"""
                    DELETE FROM lottery_results
                    WHERE lottery_type = %s AND id IN ({placeholders})
                """, [lottery_type] + [record[0] for record in invalid_records])
                
                deleted_results = cursor.rowcount
                print(f"    - 已删除 {deleted_results} 条开奖记录")
                
                total_deleted += deleted_results
            else:
                print(f"    - [模拟] 将删除 {len(invalid_records)} 条开奖记录及其相关遗漏数据")
                # 显示前5条作为示例
                print(f"    - 示例（前5条）：")
                for record in invalid_records[:5]:
                    print(f"      ID: {record[0]}, 期号: {record[1]}, 日期: {record[2]}")
        else:
            print(f"  ✅ 无无效记录")
    
    if not dry_run:
        # 提交事务
        conn.commit()
        print(f"\n{'=' * 100}")
        print(f"✅ 清理完成！共删除 {total_deleted} 条记录")
        print('=' * 100)
    else:
        print(f"\n{'=' * 100}")
        print(f"【模拟运行完成】发现 {total_found} 条无效记录")
        print("如需正式执行，请使用参数: --execute")
        print('=' * 100)
    
    cursor.close()
    conn.close()
    
    return total_deleted if not dry_run else total_found

if __name__ == '__main__':
    import argparse
    
    parser = argparse.ArgumentParser(description='清理期号与日期不匹配的无效记录')
    parser.add_argument('--execute', action='store_true', 
                       help='正式执行清理（默认为模拟运行）')
    
    args = parser.parse_args()
    
    if not args.execute:
        print("\n⚠️  当前为模拟运行模式，不会真正删除数据")
        print("如需正式执行，请添加 --execute 参数\n")
    else:
        # 确认操作
        print("\n⚠️  警告：即将删除数据库中的无效记录！")
        print("请确保已运行 02_backup_database.py 进行备份")
        confirm = input("\n是否继续？(输入 YES 确认): ")
        
        if confirm != "YES":
            print("❌ 操作已取消")
            sys.exit(0)
    
    total = clean_invalid_records(dry_run=not args.execute)
    
    if args.execute and total > 0:
        print("\n下一步：运行 04_recalculate_omission.py 重新计算遗漏")

